package com.itheima.health.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface ReportMapper extends BaseMapper {
    @Select("SELECT concat(rd.fyear,'-',rd.fmonth) AS yearAndMonths,count(tm.ID) AS memberCounts " +
            "FROM rpt_date rd,t_member tm " +
            "WHERE rd.fdate=tm.REGTIME " +
            "AND rd.fdate " +
            "BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY rd.fyear,rd.fmonth")
    List<Map> memberReportByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);

    @Select("select COUNT(ts.id) as value ,ts.name " +
            "from t_order tor, t_setmeal ts " +
            "where tor.setmeal_id=ts.id " +
            "group by ts.id")
    List<Map> setmealProportion();

    @Select("SELECT count(1) value,(CASE WHEN sex=0 THEN '女性' ELSE '男性' END) AS name " +
            "FROM t_member " +
            "GROUP BY sex")
    List<Map> findMemberSex();

    @Select("SELECT count(age) value, " +
            "(CASE WHEN age<=18 THEN '0-18岁'  " +
            " WHEN age BETWEEN 19 AND 30 THEN '19-30岁' " +
            " WHEN age BETWEEN 31 AND 45 THEN '31-45岁' " +
            "WHEN age>45 THEN '45岁以上' END) AS name " +
            "FROM (SELECT (year(now())- substring(IDCARD,7,4)) age  " +
            "FROM t_member) AS trem " +
            "GROUP BY name")
    List<Map> findMemberAge();

}
